数据操作语言

Data Manipulation Languages.

修改数据表

插入数据记录

INSERT INTO table_name [(col_name,...)]
                 VALUES (col_value,...),(...),...
                 WHERE conditions;

MySQL可省略INTO关键字。

如果未指定列名，则需按列定义顺序给出所有列的值；反之，只需给出指定的列名对应的值（未指定的列的值为NULL）。

非数值类型：使用单引号包含起来，以免发生错误。

批量插入记录

从表格复制

INSERT INTO target_table (SELECT * from source_table);

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE INTO table_name [(col_name,...)]
                 VALUES (col_value,...),(...),...;

INSERT IGNORE table_name ...;

忽略导致错误的行，并将其余行插入到表中。

更新记录字段

将满足条件的表项的字段值更新为设定值。

UPDATE table_name 
SET col_name1=value1[, col_name2=expr1] ...
[WHERE where_condition]

删除数据记录

删除单表记录

DELETE FROM tbl_name [WHERE where_condition];

清空表内容

TRUNCATE TABLE tbl_name -- 类似于使用`DELETE`删除所有记录。

如果该表有外键约束，则可临时禁用外键约束再清空表内容（可能破坏关联表的数据完整性，需要同时处理相关表）。

SET FOREIGN_KEY_CHECKS = 0; -- 当前会话禁用约束检查

删除多表的记录

DELETE tbl_name[.*] [, tbl_name[.*]] ...
FROM   table_references
[WHERE where_condition];

示例：

DELETE t1,t2 FROM store_info t1,store_geo t2 where t1.store_name='Boston' AND t2.store_name='Boston';

注意多表时，FROM后面如果使用了表的别名，则DELETE之后也要使用表的别名，否则会出现错误。无论是单表还是多表，如果不加WHERE作为条件限制将会导致表的记录全部被删除，因此要小心。

查询数据

查询使用SELECT命令。一般语法：https://dev.mysql.com/doc/refman/8.0/en/select.html。

SELECT [DISTINCT] field1, field2 AS Name, FUNC(field3), ...
   FROM table_reference 
   [WHERE condition]
   [GROUP BY col_list]
   [ORDER BY col_list]
   [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
   [LIMIT [offset], num_rows];

使用*代替字段名称，用于查询所有列，AS为返回结果的变量设置列名。查询语句中不仅可以直接使用变量，也可以将函数应用于查询字段或条件中的字段。

SELECT DISTINCT关键字用于查询不重复的记录。

条件过滤

通常基于字段的值构造逻辑表达式，表达式返回结果长度与表格行数一致（返回结果也可以是标量）。

SELECT * FROM store_info WHERE sales<1000;
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

条件表达式可以使用比较运算符：“=”（等于）、“>”、“<”、“>=”、“<=”、“!=”/“<>”（不等于）。

不支持“==”。

空值判断运算符：IS NULL和IS NOT NULL ，空值不支持比较运算符。

时间日期字段可以和字面值进行比较：start_time>='2021-11-01'。

注意：条件过滤的执行先于SELECT，因此在SELECT语句中声明的查询结果别名无法在WHERE语句中使用。如果查询条件需要用到SELECT计算出的列，可先执行[子查询](# 查询表引用子查询结果)，再从子查询结果中指定过滤条件再次查询。

复合条件

多个表达式之间还可以使用 AND、OR和NOT等逻辑运算符进行多条件联合查询。

模糊查询

SELECT * FROM table_name WHERE columnN LIKE pattern;

pattern支持通配符：

%：零个或多个字符；
_：一个字符；
[]：任何一个在括号中的字符，[^abc]任何不在括号中的字符，[a-z]指定字符区间。

Hive：LIKE支持函数式调用LIKE(A,B)。

正则表达式

MySQL需要8.0版本支持。

正则表达式函数

REGEXP_LIKE(expr, pat[, match_type])：匹配返回1，否则返回0；若参数为NULL，则返回NULL；REGEXP和RLIKE是作为运算符的同义词，即

key REGEXP pattern
expr NOT_REGEXP pattern, NOT (expr REGEXP pattern)

Hive: REGEXP , RLIKE返回true|false|null，同时也支持函数式调用。

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

pos：匹配起始位置，默认为1； occurrence：表示返回匹配的序号，默认0表示返回全部匹配；

Hive语法为：REGEXP_EXTRACT(colname, 'pattern', index)，其中index为0表示返回匹配结果，index>0表示返回对应的捕获组。

select REGEXP_SUBSTR(store_name, '10\.12.*') from test_type 
	where store_name REGEXP '10\.12.*';

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

排序和限制

SELECT * FROM table_name 
    ORDER BY field1 [DESC|ASC],field2 [DESC|ASC]...
    LIMIT [offset_start,] row_count ;

根据给出字段，对选出的查询记录进行排序，可以指定升序或降序。如果排序后只希望显示结果的一部分，则可以使用limit关键字来实现。

记录的索引是从0开始的，如果没有声明offset_start，则默认从第0条显示。 limit经常和order by一起使用，用于分页显示。

运算

算术运算

SELECT 1*2*3 as a, 1+2+3 as b;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

字符串运算

大小写转换

SELECT UPPER(Name) FROM Customers where UPPER(Name)='GARY';  -- LOWER

JSON函数和运算符

The main difference of json and jsonb data type is json will store the data into the plain text format while jsonb data type will store the data in binary format.

PostgreSQL

SELECT json_field::json->0     as f FROM table;  # 获取JSON数组的元素
SELECT json_field::json->'key' as f FROM table;  # 获取JSON对象的值
SELECT json_field::json#>'{key_idx,subkey_idx,...}' as f FROM table;  # 查询嵌套对象的元素

另外，运算符->>,#>>将返回值转换为文本类型。

jsonb类型支持比较运算符而json类型不支持，此外jsonb还支持以下特殊比较运算。

json_a::jsonb @> json_b::jsonb  # A是否包含B，"<@" 反之
json_a::jsonb ? 'key'           # A的顶层键名/元素是否包含'key'
json_a::jsonb ?| array['key1', 'key2'] # 是否包含任意键名/元素，"?&"包含所有键名/元素

JSON处理函数。

连接

当需要同时显示多个表中的字段时，就可以使用连接来实现。连接可分为内连接和外连接。它们之间最主要的区别在于内连接只选出表中互相匹配的记录，而外连接会选出其他不匹配的记录。

内连接

SELECT * | T1.cm [AS A1],T2.cn [AS A2][,...]
	FROM table_1 [AS] T1,table_2 [AS] T2
	WHERE T1.cx=T2.cy;
SELECT * FROM weather 
  [INNER] JOIN cities 
  ON (weather.city = cities.name);

当两个表存在相同列名时，需要使用表名（或别名）来区分引用同名列。

在使用内连接时，必须注意WHERE语句的条件一定要是两个表的匹配条件，如果该条件是错误的（不匹配），则会产生笛卡尔连接，导致产生巨大的查询结果。

外连接

外连接又分为：

全连接：包含所有
左连接：包含所有左边表中的记录，右表没有的记录填充NULL；
右连接：包含右边表中的所有记录，左表没有的记录填充NULL；

外连接语法：

SELECT * FROM table_1 [[AS] T1]
    [LEFT|RIGHT|FULL] OUTER JOIN table_2 [[AS] T2]
    ON (T1.cx=T2.cy);

交换FROM后表的顺序，则左连接和右连接可以相互转换。

子查询

查询语句中引用的数据，例如表或查询条件的值，可以是其他查询的返回值。

查询表引用子查询结果

SELECT * FROM ( SELECT sql_func(col) AS F FROM table) AS T where F = 'some_value';

查询条件引用子查询结果

SELECT * FROM table_name WHERE field_n OPERATOR (sub_select)
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather)；

视图

将查询语句保存为视图。相比于子查询，方便后续重复引用。

CREATE VIEW myview AS
    SELECT col_names FROM weather, cities WHERE city = name;
SELECT * from myview;

聚合与分组

SELECT [field,...] agg_func(field_i)	FROM table_name
	[GROUP BY field1,field2,...,fieldn [WITH ROLLUP]]
	[HAVING where_condition]

agg_func：表示聚合函数，即对表的某一列进行的统计运算。常用的有sum、count（计数）、max（最大值）、min（最小值）。 GROUP BY：对查询的表首先根据字段进行分组，然后对每个子类进行运算统计（聚合）。WITH ROLLUP（汇总）：表示将分组统计结果作为一个整体，再次进行聚合运算，并将结果附加到分组计算结果之后。 HAVING：类似于WHERE，只是HAVING是在聚合之后对结果进行过滤，而WHERE是在聚合之前对原表进行过滤。

Window Functions

进行分组并调用聚合函数，但聚合结果将关联到对应组的所有行，而非聚合为单条记录。

SELECT depname, salary, avg(salary) OVER (PARTITION BY depname) 
  FROM empsalary;

事务（Transactions）

将多个操作步骤组合到一起形成原子操作，保证该组操作仅处于完全完成或未执行的状态。

A transactional database guarantees that all the updates made by a transaction are logged in permanent storage (i.e., on disk) before the transaction is reported complete.

BEGIN;
-- SQL statements
ROLLBACK; -- cancel the transaction
COMMIT; -- complete the transaction

Learning Programming Book